<?php
/**
 * 供应商银行帐号信息审核管理
 * Created by PhpStorm.
 * User: z
 * Date: 14-9-1
 * Time: 下午8:52
 */
class Controller_suppliersku extends Controller_Admin
{
    /**
     * 权限检查
     */
    public function before()
    {
        try{
            ACL::Required('supplierSku_select');
        }catch(HTTP_Exception_403 $e){
            Message::error('sorry，你没有权限进这个目录');
            $this->request->redirect(URL::base(TRUE));
        }
        include_once(MODPATH.'/common/logs.php');
        parent::before();
    }

    // 显示供应商sku信息
    public function action_index()
    {
        $this->title = __('供应商SKU信息');
        try{
            $payway_all = Payway::get_payway_lists_active();
            $coins_arr = supplierbank::getCoins_arr();          // 币种信息
            $condtion = array();
            if(isset($_GET['supplier_id']) && intval($_GET['supplier_id']) > 0) {
                $condtion['supplier_id'] = intval($_GET['supplier_id']);
            }
            if(isset($_GET['sku']) && trim($_GET['sku'])) {
                $condtion['sku'] = trim($_GET['sku']);
            }
            if(isset($_GET['supplier_name']) && trim($_GET['supplier_name'])) {
                $condtion['supplier_name'] = trim($_GET['supplier_name']);
            }
            $count = supplierskus::count($condtion);
            $pagination = Pagination::factory(array(
                'items_per_page'  => 20,
                'total_items'     => $count,
                'uri'	          => $this->request->uri(),
            ));
            if(isset($_GET['searchdowm'])){
                $re_lists = supplierskus::query($condtion);
                $su_ids = array();
                foreach ($re_lists as $v){
                    $su_ids[] = $v['supplier_id'];
                }
                $this->down_xls($re_lists);
                exit;
            } else {
                $condtion['limit'] = $pagination->items_per_page?$pagination->items_per_page:100;
                $condtion['offset'] = $pagination->_offset?$pagination->_offset:0;
                $re_lists = supplierskus::query($condtion);
            }
        } catch(Database_Exception $e) {
            $re_lists = NULL;
            Message::error('系统出错,请联系IT！');
        }
        $view = View::factory('supplierSku/index');
        $view->set('payway_all',$payway_all);
        $view->set('re_lists',$re_lists);
        $view->set('condtion',$condtion);
        $view->set('pagination',$pagination);
        $view->set('coins_arr',$coins_arr);
        $this->response->body($view);
    }
    
    // 供应商SKU信息下载
    protected function down_xls($data) {
        require_once(Kohana::find_file('vendor', 'PHPExcel/PHPExcel/IOFactory'));
        $this->objReader = PHPExcel_IOFactory::createReader('Excel5');
        $objPHPExcel = new PHPExcel();
        $objPHPExcel->getProperties()->setCreator("newerp")
            ->setLastModifiedBy("newerp")
            ->setTitle("Office 2003 XLSX Test Document")
            ->setSubject("Office 2003 XLSX Test Document")
            ->setDescription("Test document for Office 2003 XLSX, generated using PHP classes.")
            ->setKeywords("office 2003 openxml php")
            ->setCategory("Test result file");
        $day = date("ymd");
        $objActSheet = $objPHPExcel->setActiveSheetIndex(0);
        $name =  $day . '供应商SKU信息.xls';
        $objActSheet->getColumnDimension( 'B')->setAutoSize(true);
        $objActSheet->getColumnDimension( 'C')->setAutoSize(true);
        $objActSheet->setCellValue('A1', '供应商ID');
        $objActSheet->setCellValue('B1', '供应商名称');
        $objActSheet->setCellValue('C1', 'SKU');
        $objActSheet->setCellValue('D1', '名称');
        $objActSheet->setCellValue('E1', '退税');
        $objActSheet->setCellValue('F1', '退税率');
        $objActSheet->setCellValue('G1', '加点税率');
        $objActSheet->setCellValue('H1', '单价');
        $objActSheet->setCellValue('I1', '规格');
        $objActSheet->setCellValue('J1', '单位');
        $objActSheet->setCellValue('K1', '交期');
        $objActSheet->setCellValue('L1', '备注');
        $objActSheet->setCellValue('M1', '添加时间');
        foreach ($data as $k =>$v){
            $objActSheet->setCellValue('A'.($k+2), $v['supplier_id']);
            $objActSheet->setCellValue('B'.($k+2), $v['supplier_name']);
            $objActSheet->setCellValue('C'.($k+2), $v['sku']);
            $objActSheet->setCellValue('D'.($k+2), $v['productname']);
            $objActSheet->setCellValue('E'.($k+2), $v['export_tax_rebate']==1?'是':'否');
            $objActSheet->setCellValue('F'.($k+2), '0.170');
            $objActSheet->setCellValue('G'.($k+2), $v['real_tax_rate']);
            $objActSheet->setCellValue('H'.($k+2), $v['price']);
            $objActSheet->setCellValue('I'.($k+2), $v['size']);
            $objActSheet->setCellValue('J'.($k+2), $v['nuit']);
            $objActSheet->setCellValue('K'.($k+2), $v['delivery']);
            $objActSheet->setCellValue('L'.($k+2), $v['remark']);
            $objActSheet->setCellValue('M'.($k+2), $v['create_time']);
        }
        $objPHPExcel->getActiveSheet()->setTitle('load');
        $objPHPExcel->setActiveSheetIndex(0);
        ob_end_clean();
        header("Content-Type: application/vnd.ms-excel; charset=utf-8");
        header('Content-Disposition: attachment;filename="' . $name . '"');
        header('Cache-Control: max-age=0');
        $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
        $objWriter->save('php://output');
        exit;
    }


    /**
     * 描述:模板下载
     * 作者:kelvin
     */
    public function action_temp_down() {
        $tplfile = DOCROOT . 'xlstemplate/supplierSku.xls';
        $pathinfo = pathinfo($tplfile);
        $filename = $pathinfo['basename'];
        if ( function_exists('mime_content_type') ) {    
            $mineType = mime_content_type( $tplfile );   
        } else {
            $mineType = 'application/vnd.ms-excel';
        }
        $filename = "供应商SKU信息上传模板";
        header( "Pragma: public" );
        header( "Expires: 0" ); // set expiration time    
        header( "Cache-Component: must-revalidate, post-check=0, pre-check=0" );    
        header( "Content-type:" . $mineType);    
        header( "Content-Length: " . filesize($tplfile) );    
        header( "Content-Disposition: attachment; filename=\"$filename\"" );    
        header( 'Content-Transfer-Encoding: binary' );    
        readfile($tplfile);
        exit;
    }
    /**
     * 描述:选择导出
     * 作者:kelvin
     */
    public function action_click_down() {
        if($this->request->is_get()) {
            $id = array_map('intval', explode(',', $_GET['id']));
        } elseif ($this->request->is_post()) {
            $id = array_map('intval', (array)$_POST['id']);
        }
        $id = array_filter($id);
        if(empty($id)) {
            Message::error('请选择需要导出的数据');
            $this->request->redirect(Route::get('supplier')->uri(array('controller' => 'suppliersku', 'action' => 'index')));
        } 
        $condtion['id'] = $id;
        $re_lists = supplierskus::query($condtion);
        $this->down_xls($re_lists);
    }
    /**
     * 描述:上传更新插入
     * 作者:kelvin
     */
    public function action_upload() {
        $redirect = Route::get('supplier')->uri(array('controller' => 'suppliersku', 'action' => 'index'));
        set_time_limit(0);
        ini_set('memory_limit', '1152M');
        ini_set('max_allowed_packet', '33M');
        require_once(Kohana::find_file('vendor', 'PHPExcel/PHPExcel/IOFactory'));
        $xls = $_FILES['xls']['tmp_name'];
        $objPHPExcel = PHPExcel_IOFactory::load($xls);
        $data = $objPHPExcel->getActiveSheet()->toArray(null, true, true, true);
        if(count($data)==0) {
            Message::error('数据为空，请检查后导入');
            $this->request->redirect($redirect);
        }
        $title = array(
            '供应商ID'  => 'supplier_id',
            'SKU'       => 'sku',
            '单价'      => 'price',
            '规格'      => 'size',
            '单位'      => 'nuit',
            '交期'      => 'delivery',
            '计量单位'  => 'nuit',
            '是否退税'  => 'export_tax_rebate',
            '加点税率'  => 'real_tax_rate',
            '备注'      => 'remark',
        );
        if(!isset($data[1])) {
            Message::error('数据文件格式不正确');
            $this->request->redirect($redirect);
        }
        $values = $fields = $supplier_ids = $skus = array();
        foreach($data[1] as $key => $var) {
            $val = trim($var);
            if(!isset($title[$val])) continue;
            $fields[$key] = $title[$val];
        }
        $diff = array_diff($title, $fields);
        if(!empty($diff)) {
            $diff_titles = array();
            $vars = array_flip($title);
            foreach($diff as $k) {
                $diff_titles[] = $vars[$k];
            }
            Message::error('数据文件缺少列：' . implode(',', $diff_titles) . '，建议使用模板');
            $this->request->redirect($redirect);
        }
        unset($data[1]);
        if(empty($data)) {
            Message::error('数据为空');
            $this->request->redirect($redirect);
        }
        foreach($data as $rowid => $row) {
            $value = array();
            foreach($row as $key => $val) {
                $value[$fields[$key]] = trim($val);
            }
            if(empty($value['sku']) || !is_numeric($value['supplier_id']) || !is_numeric($value['real_tax_rate'])) {
                Message::error("第{$rowid}行数据不正确（SKU、供应商ID或加点税率不正确）");
                $this->request->redirect($redirect);
            }
            if($value['real_tax_rate'] > 0.17) {
                Message::error("第{$rowid}行加点税率错误（不能大于0.17）");
                $this->request->redirect($redirect);
            }
            $skus[] = $value['sku'];
            $supplier_ids[] = intval($value['supplier_id']);
            $values[] = $value;
        }
        $skus = array_unique($skus);
        $supplier_ids = array_unique($supplier_ids);
        $skulist = skusystemapi::getSkuCnname($skus);
        $res = supplierskus::getSuppliers(array('id','name'),array('id'=>'in ('.implode(",", $supplier_ids).')'));
        if($skulist === false || $res === false) {
            Message::error('SKU基础资料系统接口响应失败');
            $this->request->redirect($redirect);
        }
        $suppliers = array();
        foreach($res as $row) {
            $suppliers[$row['id']] = $row['name'];
        }
        $skus_diff = array_diff($skus, array_keys($skulist));
        $suppliers_diff = array_diff($supplier_ids, array_keys($suppliers));
        if(!empty($skus_diff)) {
            Message::error('下列SKU无法获得中文名称：' . implode(',', $skus_diff));
            $this->request->redirect($redirect);
        }
        if(!empty($suppliers_diff)) {
            Message::error('下列供应商ID可能不存在：' . implode(',', $suppliers_diff));
            $this->request->redirect($redirect);
        }
        $fieldnames = array(
            'sku','supplier_id','supplier_name','productname','price','size','nuit','export_tax_rebate','delivery',
            'remark','modifytime','isavailable','create_time','create_user','update_user','real_tax_rate'
        );
        $sql = "INSERT INTO vmi_supplier_skus (" . implode(',', $fieldnames) . ") VALUES ";
        $date = date('Y-m-d H:i:s');
        $user_id = User::active_user()->id;
        foreach($values as $key => $row) {
            $sku = $row['sku'];
            $supplier_id = (int)$row['supplier_id'];
            $value = array(
                'sku'           => addslashes($sku),
                'supplier_id'   => $supplier_id,
                'supplier_name' => addslashes($suppliers[$supplier_id]),
                'productname'   => addslashes($skulist[$sku]['name']),
                'price'         => floatval($row['price']),
                'size'          => addslashes($row['size']),
                'nuit'          => addslashes($row['nuit']),
                'export_tax_rebate' => ($row['export_tax_rebate'] == '是') ? 1 : 0,
                'delivery'      => (int)$row['delivery'],
                'remark'        => addslashes($row['remark']),
                'modifytime'    => $date,
                'isavailable'   => 1,
                'create_time'   => $date,
                'create_user'   => $user_id,
                'update_user'   => $user_id,
                'real_tax_rate' => floatval($row['real_tax_rate']),
            ); 
            $sql .= ($key ? ',' : '') . "('" . implode("','", $value) . "')";
        }
        $update_fields = array(
            'supplier_name','productname','price','size','nuit','export_tax_rebate','delivery',
            'remark','modifytime','isavailable','update_user','real_tax_rate'
        );
        $sql .= "  ON DUPLICATE KEY UPDATE ";
        foreach($update_fields as $key => $field) {
            $sql .= ($key ? ',' : '') . "{$field}=VALUES({$field})";
        }
        try {
            $result = DB::query(Database::INSERT, $sql)->execute('newerp');
        } catch (Database_Exception $e) {
            Message::error('导入失败：' . $e->getMessage());
            $this->request->redirect($redirect);
        }
        if($result) {
            Message::success('导入成功!');
        } else {
            Message::error('导入失败了!');
        }
        $this->request->redirect($redirect);
        exit;
    }
    /**
     * 描述:删除
     * 作者:kelvin
     */
    public function action_del()
    {
        include_once(MODPATH.'/common/logs.php');
        if($_GET['id']){
            if(DB::update('vmi_supplier_skus')->set(array('isavailable'=>0))->where('id','=',$_GET['id'])->execute('newerp')){
//            if(DB::delete('vmi_supplier_skus')->where('id','=',$_GET['id'])->execute('newerp')){
                logs::write_log($_GET['id'],'vmi_supplier_sku/update',1);
                echo '删除成功';die;
            }else{
                logs::write_log($_GET['id'],'vmi_supplier_sku/update',0);
                echo '删除失败';die;
            }
        }
    }
    /**
     * 描述:批量删除
     * 作者:kelvin
     */
    public function action_all_del()
    {
        if($this->request->is_get()) {
            $id = array_map('intval', explode(',', $_GET['id']));
        } elseif ($this->request->is_post()) {
            $id = array_map('intval', (array)$_POST['id']);
        }
        $id = array_filter($id);
        if(empty($id)) {
            Message::error('请选择需要删除的数据');
            $this->request->redirect(Route::get('supplier')->uri(array('controller' => 'suppliersku', 'action' => 'index')));
        } 
        include_once(MODPATH.'/common/logs.php');
        if(DB::update('vmi_supplier_skus')->set(array('isavailable'=>0))->where('id','in',$id)->execute('newerp')){
            logs::write_log($id,'vmi_supplier_sku/delete',1);
            Message::success('请选择需要删除的数据');
            $this->request->redirect(Route::get('supplier')->uri(array('controller' => 'suppliersku', 'action' => 'index')));
        }else{
            logs::write_log($id,'vmi_supplier_sku/delete',0);
            Message::error('删除失败');
            $this->request->redirect(Route::get('supplier')->uri(array('controller' => 'suppliersku', 'action' => 'index')));
        }
    }
    /**
     * 描述:修改页面
     * 作者:kelvin
     */
    public function action_update()
    {
        $view = View::factory('supplierSku/supplierSkuUpdate');
        $data = DB::select('*')->from('vmi_supplier_skus')->where('id','=',$_GET['id'])->execute('newerp')->as_array();
        $view->set('data',$data?$data[0]:array());
        $view->set('link',$_GET['link']);
        echo $view->render();die;
    }
    /**
     * 描述:修改执行
     * 作者:kelvin
     */
    public function action_update_save()
    {
        if($_POST){
            $data = array(
                'sku' => $_POST['sku'],
                'productname' => $_POST['productname'],
                'price' => $_POST['price'],
                'size' => $_POST['size'],
                'nuit' => $_POST['nuit'],
                'delivery' => $_POST['delivery'],
                'export_tax_rebate' => $_POST['export_tax_rebate'],
                'real_tax_rate' => $_POST['real_tax_rate'],
                'remark' => $_POST['remark'],
                'modifytime' => date('Y-m-d H:i:s'),
                'update_user' => User::active_user()->id,
            );
            if(DB::update('vmi_supplier_skus')->set($data)->where('id','=',$_POST['id'])->execute('newerp')){
                logs::write_log($_POST['id'],'vmi_supplier_sku/update',1);
                echo "<script>alert('修改成功');location.href='".URL::base()."supplier/supplierSku/index?".$_POST['link']."';</script>";die;
            }else{
                logs::write_log($_POST['id'],'vmi_supplier_sku/update',0);
                echo "<script>alert('修改失败');history.go(-1);</script>";die;
            }
        }

    }
    /**
     * 描述:查询供应商
     * 作者:kelvin
     */
    public function action_getSuppliers()
    {
        $condition = array('name' => ' LIKE "%'.trim($_GET['term']).'%"');
        $suppliers = supplierskus::getSuppliers(array('id','name'),$condition);
        if($suppliers)
        {
            foreach($suppliers as $supplier){
                $id        = $supplier['id'];
                $str       = trim($supplier['name']);
                $results[] = array(
                    'id'    => $id,
                    'label' => $id.":".$str,
                    'name'  => $str
                );
            }
            echo json_encode($results);
            exit;
        }

    }
}